import base64

import pymysql

from Netease.util.base64Util import base64Util

MYSQL_HOSTS='118.24.62.141'
MYSQL_USER='pei'
MYSQL_PASSWORD='123456'
MYSQL_DB='netease'
# 打开数据库连接
db = pymysql.connect(MYSQL_HOSTS, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DB)
db.set_charset('utf8')   #防止中文乱码
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
cursor.execute('SET NAMES utf8;')
cursor.execute('SET CHARACTER SET utf8;')
cursor.execute('SET character_set_connection=utf8;')

class Sql:

    @classmethod
    def insert_or_update_user(cls,userId,nickname,avatarUrl,gender,signature,follows,followeds,eventCount,playlistCount,level,totalCount,userType,area):
        # 处理可能会出现单引号和双引号的地方
        if nickname:
            nickname = nickname.replace("'", "\\\'")
            nickname = nickname.replace('"', '\\\"')
        if signature:
            signature = signature.replace("'", "\\\'")
            signature = signature.replace('"', '\\\"')
        sql = "insert into user ( userId,nickname,avatarUrl,gender,signature,follows,followeds,eventCount,playlistCount,level,totalCount,userType,area)" \
              " values ('%s', '%s', '%s', '%d','%s', '%d', '%d', '%d', '%d' , '%d', '%d', '%d', '%s') on duplicate key update nickname=VALUES(nickname),avatarUrl=VALUES(avatarUrl),gender=VALUES(gender),signature=VALUES(signature),follows=VALUES(follows),followeds=VALUES(followeds),eventCount=VALUES(eventCount),playlistCount=VALUES(playlistCount),level=VALUES(level),totalCount=VALUES(totalCount),userType=VALUES(userType),area=VALUES(area)" \
              % (userId,nickname,avatarUrl,gender,signature,follows,followeds,eventCount,playlistCount,level,totalCount,userType,area)
        # 在每次运行sql之前，ping一次，如果连接断开就重连
        db.ping(reconnect=True)
        cursor.execute(sql)
        db.commit()


    @classmethod
    def insert_or_update_record(cls,userId,top):
        # 因为json存在大量单引号，所以转为base64 存入
        top = base64Util.encryption(top)

        sql = "insert into record(userId,top) values ('%d','%s') on duplicate key update top=VALUES(top)"%(userId,top)
        # 在每次运行sql之前，ping一次，如果连接断开就重连
        db.ping(reconnect=True)
        cursor.execute(sql)
        db.commit()

    @classmethod
    def insert_or_update_song(cls,songId,songname,artists,albumId,albumname,comments,mvId):
        # 因为json存在大量单引号，所以转为base64 存入
        artists = base64Util.encryption(artists)

        sql = "insert into song(songId,songname,artists,albumId,albumname,comments,mvId) values ('%d','%s','%s','%d','%s','%d','%d') on duplicate key update songname=VALUES(songname),artists=VALUES(artists),albumId=VALUES(albumId),albumname=VALUES(albumname),comments=VALUES(comments),mvId=VALUES(mvId)"%(songId,songname,artists,albumId,albumname,comments,mvId)
        # 在每次运行sql之前，ping一次，如果连接断开就重连
        db.ping(reconnect=True)
        cursor.execute(sql)
        db.commit()